﻿using Star_Helper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

/// <summary>
/// DoDB 的摘要说明
/// </summary>
public class DoDB
{
    private string dbx;
    public DoDB()
    {
        dbx = "Server=WHALE\\WSQL;DataBase=ligoudan;uid=sa;pwd=123456;";//数据库连接配制
        //dbx = "Server=BEAR\\BSQL;DataBase=ligoudan;uid=sa;pwd=123456;";//数据库连接配制
    }

    /// <summary>
    /// 是否存在数据库
    /// </summary>
    /// <param name="sx"></param>
    /// <returns></returns>
    public bool hasSqlTable(string sx)
    {
        if (new dataFunc().testColVal("sysobjects", "name", sx) == 1)
        {
            dbLog("存在数据库"); return true;
        }
        else
        {
            dbLog("不存在数据库"); return false;
        }
    }

    /// <summary>
    /// 初始化,生成数据库
    /// </summary>
    public void initSqlTable()
    {
        //用户表   键 , 名 , 密码 , 权限 , 手机 , 邮箱 , 
        SqlEx(@"CREATE TABLE TSyonghu ( id int IDENTITY(0,1) PRIMARY KEY  ,  
           yonghu varchar(max), mima varchar(max), quanxian  int, zhuangtai  int, shouji int,youxiang varchar(max),)");
        //商品表   键 ,名 , 类别 , 单价, 成本 , 入库 ,出库, 盈利  , 库存 
        SqlEx(@"CREATE TABLE  TSshangpin ( id int IDENTITY(0,1) PRIMARY KEY  ,  
           shangpin varchar(max) default '-', leibie varchar(max) default '-', danjia int default 0, chengben int default 0,ruku int default 0,chuku int default 0,yingli int default 0,kucun int default 0 )");
        //操作记录表  键 , 商品 ,单价, 成本价 , 数量 , 总计 , 操作员 , 日期 , 操作类型
        SqlEx(@"CREATE TABLE  TScaozuo ( id int IDENTITY(0,1) PRIMARY KEY  ,  
           shangpin varchar(max), danjia varchar(max), chengben varchar(max), shuliang int , zongji int,yonghu varchar(max),riqi varchar(max),caozuo int)");
    }

    /// <summary>
    /// 测试:生成商品
    /// </summary>
    public void initTestValue()//插入一些测试数据
    {
        Random ran = new Random(6);
        for (int i = 0; i < 5; i++)
        {
            for (int k = 0; k < 20; k++)
            {
                DateTime dt = DateTime.Now;// dt.ToString();//2005-11-5 13:21:25
                int chengben = ran.Next(1, 50);//成本
                int shuliang = ran.Next(1, 500);//数量
                int lirun = ran.Next(2, 20);//利润,
                int danjia = chengben + lirun;//售价
                int zonge = chengben * shuliang;//支出总额
                                                
                depositShangpin("商品" + i + k, "类别" + i, danjia, chengben, 0, 0, 0, 0);
            }
        }
    }
    /*
    public void initTestJulu()
    {
        Random ran = new Random(6);
        for (int i = 0; i < 6; i++)
        {
            for (int k = 0; k < 20; k++)
            {

            }
        }
    }
    */
    public bool hasYonghu(string sx)
    {
        if (new dataFunc().testColVal("TSyonghu", "yonghu", sx) == 1)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    public bool hasLogin(string yonghu, string mima)
    {
        if (new dataFunc().testColVal("TSyonghu", "mima", mima, string.Format("AND yonghu='{0}'", yonghu)) == 1)
        {
            clearLogin();
            dataFunc d = new dataFunc();
            d.addNewupSql(2, "TSyonghu", "zhuangtai", "N:1", string.Format("yonghu='{0}'", yonghu));//设置登陆状态
            d.execSqlsTran();
            return true;
        }
        else
        {
            return false;
        }
    }
    public void clearLogin()
    {
        dataFunc d = new dataFunc();
        d.addNewupSql(2, "TSyonghu", "zhuangtai", "N:0", "zhuangtai=1");//清除登陆状态
        d.execSqlsTran();
    }
    public void addYonghu(string yonghu, string mima)
    {
        clearLogin();
        dataFunc d = new dataFunc();
        d.addNewupSql(1, "TSyonghu", "yonghu,mima,quanxian,zhuangtai", string.Format("T:{0};T:{1};N:1;N:1", yonghu, mima));
        d.execSqlsTran();
    }

    public void depositZongE()//存入总额,在这里进行一些核算
    {

    }

    /// <summary>
    /// 插入商品,新的商品品种,
    /// </summary>
    /// <param name="shangpin"></param>
    /// <param name="leibie"></param>
    /// <param name="danjia"></param>
    /// <param name="chengben"></param>
    /// <param name="ruku"></param>
    /// <param name="chuku"></param>
    /// <param name="yingli"></param>
    /// <param name="kucun"></param>
    public void depositShangpin(string shangpin, string leibie, int danjia, int chengben, int ruku, int chuku, int yingli, int kucun)
    {
        dataFunc d = new dataFunc();
        string s = string.Format("T:{0};T:{1};N:{2};N:{3};N:{4};N:{5};N:{6};N:{7}", shangpin, leibie, danjia, chengben, ruku, chuku, yingli, kucun);
        d.addNewupSql(1, "TSshangpin", "shangpin,leibie,danjia,chengben,ruku,chuku,yingli,kucun", s);
        d.execSqlsTran();
    }


    /// <summary>
    /// 操作,存入记录
    /// </summary>
    /// <param name="i">操作记录的类型,1入库,2出库</param>
    /// <param name="shangpin">商品</param>
    /// <param name="chengben">成本,或单价</param>
    /// <param name="shuliang">数量</param>
    /// <param name="zongji">数量乘以成本或单价的总额</param>
    /// <param name="yonghu">操作的用户</param>
    /// <param name="riqi">操作日期</param>
    public void depositCaozuo(int i, string shangpin, int chengbendanjia, int shuliang, int zongji)//操作记录,先提取旧的加完再存(只能对已有商品,应增加商品新建)
    {
        string yonghu = takeDangqianYH();
        DateTime dt = DateTime.Now;
        string riqi = dt.ToString();
        dataFunc d = new dataFunc();
        switch (i)
        {
            case 1:
                string s1 = string.Format("T:{0};N:{1};N:{2};N:{3};T:{4};T:{5};N:1", shangpin, chengbendanjia, shuliang, zongji, yonghu, riqi);
                d.addNewupSql(1, "TScaozuo", "shangpin,chengben,shuliang,zongji,yonghu,riqi,caozuo", s1);
                break;
            case 2:
                string s2 = string.Format("T:{0};N:{1};N:{2};N:{3};T:{4};T:{5};N:2", shangpin, chengbendanjia, shuliang, zongji, yonghu, riqi);
                d.addNewupSql(1, "TScaozuo", "shangpin,danjia,shuliang,zongji,yonghu,riqi,caozuo", s2);
                break;
            default:
                break;
        }

        d.execSqlsTran();
    }
    public void caozuoR(string sx, int shuliang)//入库操作计算的是成本
    {
        List<string> list = takeSpDC(sx);
        int chengben = int.Parse(list[0]);
        int zongji = chengben * shuliang;
        depositCaozuo(1, sx, chengben, shuliang, zongji);
        dispotAmount(1, sx, chengben, shuliang);
    }
    public void caozuoC(string sx, int shuliang)//出库操作计算的是单价 ,出库要判断库存为0时(未做)
    {
        List<string> list = takeSpDC(sx);
        int chengben = int.Parse(list[1]);
        int zongji = chengben * shuliang;
        depositCaozuo(2, sx, chengben, shuliang, zongji);
        dispotAmount(2, sx, chengben, shuliang);
    }

    /// <summary>
    /// 更新商品的总额,1为入库,2为出库
    /// </summary>
    /// <param name="i"></param>
    /// <param name="shangpin"></param>
    /// <param name="cd"></param>
    /// <param name="shuliang"></param>
    public void dispotAmount(int i, string shangpin,int cd, int shuliang)
    {
        switch (i)
        {
            case 1:
                SqlEx(string.Format(@"UPDATE  [dbo].[TSshangpin] SET [kucun] = [kucun]+{0} 
                    ,[ruku] = [ruku]+{0}  WHERE shangpin = '{1}'", shuliang, shangpin));

                SqlEx(string.Format(@"UPDATE  [dbo].[TSshangpin] SET  [kucun] = [kucun]+{0}  
      ,[ruku] = [ruku]+{0} WHERE id = 0", shuliang, shangpin));

                break;
            case 2:
                SqlEx(string.Format(@"UPDATE  [dbo].[TSshangpin] SET [kucun] = [kucun]-{0} 
    ,[yingli] = ([danjia] -[chengben]) *{0} ,[chuku] = [chuku]+{0}  WHERE shangpin = '{1}'", shuliang, shangpin));

                SqlEx(string.Format(@"UPDATE  [dbo].[TSshangpin] SET [kucun] = [kucun]-{0} 
    ,[yingli] = ([danjia] -[chengben]) *{0} ,[chuku] = [chuku]+{0} WHERE id = 0", shuliang, shangpin));

                break;
            default:
                break;
        }
    }


    /// <summary>
    /// 得到商品的单价和成本
    /// </summary>
    public List<string> takeSpDC(string sx)
    {
        dataFunc d = new dataFunc();
        return d.colsList("TSshangpin", "danjia,chengben", string.Format("shangpin='{0}'", sx));

    }
    /// <summary>
    /// 得到当前登陆的用户
    /// </summary>
    /// <returns></returns>
    public string takeDangqianYH()
    {
        return new dataFunc().colVal("TSyonghu", "yonghu", "zhuangtai=1");
    }

    /// <summary>
    /// 得到总额
    /// </summary>
    /// <returns></returns>
    public object[] takeZongE()
    {
        dataFunc d = new dataFunc();
        List<object[]> list = d.colsListM("TSshangpin", "ruku,chuku,yingli,kucun", "id=0");
        object[] list2 = list[0];
        return list2;
    }

    /// <summary>
    /// 得到全部某类别下的商品名
    /// </summary>
    /// <returns></returns>
    public List<string> takeSp(string sx)
    {
        dataFunc d = new dataFunc();
        List<string> list2 = new List<string>();
        List<object[]> list = d.colsListM("TSshangpin", "shangpin", string.Format("id>0 AND leibie='{0}'", sx));
        for (int i = 0; i < list.Count; i++)
        {
            list2.Add((string)list[i][0]);
        }
        return list2;
    }

    /// <summary>
    /// 得到种类
    /// </summary>
    /// <returns></returns>
    public List<string> takeLeiBie()//得到种类
    {
        dataFunc d = new dataFunc();
        List<string> list2 = new List<string>();
        List<object[]> list = d.colsListM("TSshangpin", "DISTINCT leibie", "id>0");
        for (int i = 0; i < list.Count; i++)
        {
            list2.Add((string)list[i][0]);
        }
        return list2;
    }

    /// <summary>
    /// 得到某个商品的明细
    /// </summary>
    /// <param name="sx"></param>
    /// <returns></returns>
    public List<string> takeShangPin(string sx)
    {
        dataFunc d = new dataFunc();
        return d.colsList("TSshangpin", "shangpin,danjia,chengben,ruku,chuku,yingli,kucun", string.Format("shangpin='{0}'", sx));
    }

    /// <summary>
    /// 得到入库记录
    /// </summary>
    /// <returns></returns>
    public List<object[]> takeJiLuR()
    {
        dataFunc d = new dataFunc();
        return d.colsListM("TScaozuo", "shangpin,chengben,shuliang,zongji,yonghu,riqi", "caozuo=1");
    }

    /// <summary>
    /// 得到出库记录
    /// </summary>
    /// <returns></returns>
    public List<object[]> takeJiLuC()
    {
        dataFunc d = new dataFunc();
        return d.colsListM("TScaozuo", "shangpin,danjia,shuliang,zongji,yonghu,riqi", "caozuo=2");
    }

    /// <summary>
    /// 执行没有返回值的数据库语句
    /// </summary>
    /// <param name="sqlx">数据库语句</param>
    public void SqlEx(string sqlx)
    {
        using (SqlConnection conne = new SqlConnection(dbx))
        {
            conne.Open();
            using (SqlCommand comm = new SqlCommand(sqlx, conne))
            {
                try
                {
                    int rowsReturned = comm.ExecuteNonQuery();//返回受影响行数
                }
                catch (SqlException e)
                {
                    //Console.WriteLine("语句错误 --- " + e.Message);
                    dbLog(e.Message);
                }
            }

        }
    }


    /// <summary>
    /// 带参数集的数据库语句,返回DataTable
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="par"></param>
    /// <returns></returns>
    public DataTable ExecuteTable(string sql, params SqlParameter[] par)
    {
        using (SqlConnection conn = new SqlConnection(dbx))
        {
            conn.Open();
            using (SqlCommand comm = new SqlCommand(sql, conn))
            {
                foreach (SqlParameter p in par)
                {
                    comm.Parameters.Add(p);
                }
                DataSet ds = new DataSet();//数据集
                SqlDataAdapter adp = new SqlDataAdapter(comm);
                adp.Fill(ds);
                return ds.Tables[0];//可以直接在这里return
            }
        }
    }

    public void dbLog(string sx)
    {
        dataFunc.writeDoLogs("数据库错误", sx, "DoDBLog", 1);
    }
}